# @IDE: PyCharm
# @Author: Allen
# @File: dbinfo.py
# @Time: 2020/4/20 18:45

import sqlite3
import logging

logging.basicConfig(filename="./log/bridge_sql.log", filemode='w',
                    format='%(asctime)s : %(levelname)s : %(message)s',
                    level=logging.DEBUG)

CREATE_TABLE = '''
CREATE TABLE "en_translator" (
"id"  INTEGER PRIMARY KEY autoincrement NOT NULL,
"en_word"  TEXT,
"zh_word"  TEXT,
"in_count"  TEXT,
"datetime"  TEXT
);
'''


class OperateSql:
    def __init__(self):
        self.conn = sqlite3.connect('./db/dbinfo.db', check_same_thread=False)
        self.cur = self.conn.cursor()
        self.__create_table()

    def __create_table(self):
        """
        创建表
        :return:
        """
        self.cur.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
        tables = self.cur.fetchall()
        if tables:
            tab_list= []
            for t in range(len(tables)):
                tab_list.append(tables[t][0])
            if 'en_translator' not in tab_list:
                self.cur.execute(CREATE_TABLE)
                self.conn.commit()
        else:
            self.cur.execute(CREATE_TABLE)
            self.conn.commit()

    def select_key(self, key):
        """
        查询 en关键字在数据库中是否已经存在
        :param key:
        :return:
        """
        sql = "select zh_word from en_translator where en_word = '{}';".format(key)
        logging.info(sql)
        self.cur.execute(sql)
        result = self.cur.fetchall()
        logging.info("数据库中，{} 关键字存在 {}".format(key, result))
        return result

    def insert_data(self, enkey, zhword):
        """
        插入数据到数据库
        :param enkey:
        :param zhword:
        :return:
        """
        import time
        sql = "insert into en_translator (en_word, zh_word,in_count, datetime) " \
              "values ('{}','{}','0','{}');".format(enkey,zhword,time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
        logging.info(sql)
        self.cur.execute(sql)
        self.conn.commit()
